package com.tutu.utils;

import com.tutu.entity.DeviceData;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

    /**
     * 生成excel
     *
     * @param templatePath   目标文件路径
     * @param deviceDataList 导出的数据对象
     * @param exportFilePath 生成文件路径
     */
    @SuppressWarnings("Duplicates")
    public static void createExcel(String templatePath, List<DeviceData> deviceDataList, String exportFilePath) throws Exception {
        try {
            // 读取导出模板
            InputStream inp = new FileInputStream(templatePath);

            if (!inp.markSupported()) {
                inp = new PushbackInputStream(inp, 8);
            }

            if (POIFSFileSystem.hasPOIFSHeader(inp)) {
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(templatePath));
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);

                // 输出流
                OutputStream out = Files.newOutputStream(Paths.get(exportFilePath));

                // 模板数据要放在Sheet1
                HSSFSheet sheet = hssfWorkbook.getSheetAt(0);

                //设置自动列宽
                for (int i = 0; i < deviceDataList.get(0).getValueList().size(); i++) {
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 5);
                }

                HSSFRow row;
                HSSFCell cell;
                for (int i = 0; i < deviceDataList.size(); i++) {
                    row = sheet.createRow(1 + i);
                    row.setHeightInPoints(30);

                    for (int j = 0; j < deviceDataList.get(i).getValueList().size(); j++) {
                        cell = row.createCell(j);
                        cell.setCellStyle(getStyle(hssfWorkbook));
                        cell.setCellValue(deviceDataList.get(i).getValueList().get(j).getValue() + "");
                    }
                }

                hssfWorkbook.write(out);
                out.close();
            }
            if (POIXMLDocument.hasOOXMLHeader(inp)) {
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(new File(templatePath)));

                // 输出流
                OutputStream out = new FileOutputStream(exportFilePath);
                // 模板数据要放在Sheet1
                XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
                //设置自动列宽
                for (int i = 0; i < deviceDataList.get(0).getValueList().size(); i++) {
                    sheet.autoSizeColumn(i);
                    // sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 2);
                    sheet.setColumnWidth(i, 256 * 30);
                }

                XSSFRow row = sheet.createRow(0);
                row.setHeightInPoints(30);

                XSSFCell cell;

                // 设置第一栏标题
                for (int j = 0; j < deviceDataList.get(0).getValueList().size(); j++) {
                    cell = row.createCell(j);
                    cell.setCellStyle(getStyle(xssfWorkbook, true));
                    cell.setCellValue(deviceDataList.get(0).getValueList().get(j).getName() + "");
                }

                // 设置底部的内容
                for (int i = 0; i < deviceDataList.size(); i++) {
                    row = sheet.createRow(1 + i);
                    row.setHeightInPoints(30);

                    for (int j = 0; j < deviceDataList.get(i).getValueList().size(); j++) {
                        cell = row.createCell(j);
                        cell.setCellStyle(getStyle(xssfWorkbook, false));
                        cell.setCellValue(deviceDataList.get(i).getValueList().get(j).getValue() + "");
                    }
                }

                xssfWorkbook.write(out);
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String generateExcel(String sheetName, String titleName, int[] columnWidth, String fileName, String[] columnName, List<List<String>> dataList) {
        if (columnWidth.length != columnName.length) return "列数和表头数不相等";

        // 创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 设定列数
        int columnNumber = columnWidth.length;

        // 设置每一列的宽度
        for (int i = 0; i < columnWidth.length; i++)
            for (int j = 0; j <= i; j++)
                if (i == j) sheet.setColumnWidth(i, columnWidth[j] * 256); // 单独设置每列的宽

        // 设置标题
        HSSFRow row1 = sheet.createRow(0);

        // 设置标题的高度
        row1.setHeightInPoints(50);

        // 创建标题单元格样式以及字体样式
        HSSFCellStyle style = wb.createCellStyle();

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);

        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 创建字体样式
        HSSFFont headerFont1 = (HSSFFont) wb.createFont();

        // 字体加粗
        headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 设置字体类型
        headerFont1.setFontName("黑体");

        // 设置字体大小
        headerFont1.setFontHeightInPoints((short) 15);

        // 为标题样式设置字体样式
        style.setFont(headerFont1);

        // 创建第一列
        HSSFCell cell1 = row1.createCell(0);

        // 合并所有列并居中
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNumber - 1));

        // 设置值标题
        cell1.setCellValue(titleName);

        // 设置标题样式
        cell1.setCellStyle(style);

        // 设置表头
        HSSFRow row = sheet.createRow(1);

        // 设置表头高度
        row.setHeightInPoints(37);

        // 创建表头单元格样式 以及表头的字体样式
        HSSFCellStyle style1 = wb.createCellStyle();

        // 设置自动换行
        style1.setWrapText(true);

        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 居中格式
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        style1.setBottomBorderColor(HSSFColor.BLACK.index);

        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 创建字体样式
        HSSFFont headerFont = (HSSFFont) wb.createFont();

        // 字体加粗
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 设置字体类型
        headerFont.setFontName("黑体");

        // 设置字体大小
        headerFont.setFontHeightInPoints((short) 10);

        // 为标题样式设置字体样式
        style1.setFont(headerFont);

        // 创建表头的列
        for (int i = 0; i < columnNumber; i++) {
            HSSFCell cell = row.createCell(i);

            cell.setCellValue(columnName[i]);

            cell.setCellStyle(style1);
        }

        // 设置为自动换行并居中
        HSSFCellStyle lineBreak = wb.createCellStyle();

        // 设置自动换行
        lineBreak.setWrapText(true);

        // 创建一个居中格式
        lineBreak.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置边框
        lineBreak.setBottomBorderColor(HSSFColor.BLACK.index);

        lineBreak.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        lineBreak.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        lineBreak.setBorderRight(HSSFCellStyle.BORDER_THIN);

        lineBreak.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle lineBreak2 = wb.createCellStyle();

        lineBreak2.setWrapText(true);// 设置自动换行
        lineBreak2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式
        lineBreak2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

        // 设置边框
        lineBreak2.setBottomBorderColor(HSSFColor.BLACK.index);

        lineBreak2.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        lineBreak2.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        lineBreak2.setBorderRight(HSSFCellStyle.BORDER_THIN);

        lineBreak2.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 创建单元格，并设置值
        for (int i = 0; i < dataList.size(); i++) {
            // 创建行
            row = sheet.createRow(i + 2);

            HSSFCell datacell;

            for (int j = 0; j < columnNumber; j++) {
                datacell = row.createCell(j);
                datacell.setCellValue(dataList.get(i).get(j));
                datacell.setCellStyle(lineBreak2);
            }
        }

        // 文件夹路径
        String fileth1 = "E://ExcelReport/";

        //子文件夹名称
        String fileth2 = new Date().getTime() + "/";

        // 获取文件夹
        File file = new File(fileth1 + fileth2 + fileName + ".xls");

        //获取上级文件夹
        File parentFile = file.getParentFile();

        if (!parentFile.exists() && !parentFile.isDirectory()) parentFile.mkdirs();

        FileOutputStream fout = null;

        try {
            fout = new FileOutputStream(file);
            wb.write(fout);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != fout) fout.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return fileth1 + fileth2 + fileName + ".xls";
    }

    /*
     * 列数据信息单元格样式
     */
    @SuppressWarnings("Duplicates")
    static HSSFCellStyle getStyle(HSSFWorkbook hssfWorkbook) {
        // 设置字体
        HSSFFont font = hssfWorkbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("宋体");
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //设置样式;
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    static XSSFCellStyle getStyle(XSSFWorkbook xssfWorkbook, boolean isTitle) {
        // 设置字体
        XSSFFont font = xssfWorkbook.createFont();

        //设置样式;
        XSSFCellStyle style = xssfWorkbook.createCellStyle();

        if (isTitle) {
            // 字体加粗
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            //设置填充方案
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            //设置自定义填充颜色
            style.setFillForegroundColor(new XSSFColor(new Color(221, 139, 62)));
        }

        //设置字体名字
        font.setFontName("宋体");

        //设置字体大小
        font.setFontHeightInPoints((short) 12);

        //设置底边框;
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);

        //设置左边框;
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        //设置右边框;
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);

        //设置顶边框;
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);

        //在样式用应用设置的字体;
        style.setFont(font);

        //设置自动换行;
        style.setWrapText(false);

        //设置水平对齐的样式为居中对齐;
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        return style;
    }
}
